package com.easylinkin.bases.excel.util;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;
import lombok.Data;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
 * excel导出工具类
 *
 * @author yxzhouj
 * @since 2019/10/12
 */
@Data
public class ExcelExporterUtil {

  public static final String EXCEL_TYPE_XLS = "xls";
  public static final String EXCEL_TYPE_XLSX = "xlsx";
  public static final String DATA_CELL_TYPE_INTEGER = "Integer";
  public static final String DATA_CELL_TYPE_STRING = "String";
  public static final String DATA_CELL_TYPE_FLOAT = "Float";
  public static final short DATA_CELL_ALIGN_CENTER = 2;
  public static final short DATA_CELL_ALIGN_LEFT = 1;
  public static final short DATA_CELL_ALIGN_RIGHT = 3;
  protected String title = "";
  protected String description;
  protected List<String> headList;
  protected List<List<Object>> rowDataList;
  protected List<String> dataTypeList;
  protected String excelType;
  protected String sheetName;
  protected String crossHeadText = "";
  protected Integer startRow = 0;
  protected Integer startCol = 0;
  protected Integer rowNumber;
  protected Integer defaultColWidth = 20;
  protected String dataCellType = DATA_CELL_TYPE_STRING;
  protected Short dataCellAlign = Short.valueOf((short) 2);
  protected Short titleRowHeight = Short.valueOf((short) 50);
  protected Short titleFontHeight = Short.valueOf((short) 22);
  protected Short descRowHeight = Short.valueOf((short) 15);
  protected Short descFontHeight = Short.valueOf((short) 10);
  protected Short dataFontHeight = Short.valueOf((short) 10);
  protected Short headFontHeight = Short.valueOf((short) 10);
  protected Short colheadRowHeigh;
  protected Short headForegroundColor;
  protected boolean isDataFstHead;
  protected Short dataRowHeigh;
  protected Workbook destWorkbook;
  protected CellStyle titleStyle;
  protected CellStyle colHeadStyle;
  protected CellStyle rowHeadStyle;
  protected CellStyle descriptionStyle;
  protected CellStyle stringDataCellStyle;
  protected CellStyle integerDataCellStyle;
  protected CellStyle floatDataCellStyle;

  public ExcelExporterUtil() {
    this.headForegroundColor = IndexedColors.GREY_40_PERCENT.getIndex();
    this.isDataFstHead = false;
  }

  public void export(OutputStream out) throws IOException {
    Workbook workbook = this.getWorkbook();
    workbook.write(out);
  }

  public Workbook getWorkbook() {
    this.createBlankWorkbook();
    if (this.destWorkbook != null) {
      this.createCellStyles();
      this.fillTitle();
      this.fillDiscription();
      if (this.headList != null) {
        this.fillColHead();
        if (this.rowDataList != null) {
          this.fillData();
        }
      }
    }

    return this.destWorkbook;
  }

  protected void createBlankWorkbook() {
    if (EXCEL_TYPE_XLSX.equals(this.excelType)) {
      this.destWorkbook = new XSSFWorkbook();
    } else {
      if (!EXCEL_TYPE_XLS.equals(this.excelType)) {
        this.destWorkbook = null;
        return;
      }

      this.destWorkbook = new HSSFWorkbook();
    }

    this.sheetName = this.sheetName == null ? "sheet1" : this.sheetName;
    Sheet sheet = this.destWorkbook.createSheet(this.sheetName);
    sheet.setDefaultColumnWidth(this.defaultColWidth);
    this.rowNumber = this.startRow;
  }

  protected void createCellStyles() {
    this.titleStyle = this.destWorkbook.createCellStyle();
    this.titleStyle.setAlignment((short) 2);
    this.titleStyle.setVerticalAlignment((short) 1);
    Font titleFont = this.destWorkbook.createFont();
    titleFont.setFontHeightInPoints(this.titleFontHeight);
    titleFont.setBoldweight((short) 700);
    this.titleStyle.setFont(titleFont);
    this.descriptionStyle = this.destWorkbook.createCellStyle();
    this.descriptionStyle.setAlignment((short) 1);
    this.descriptionStyle.setVerticalAlignment((short) 1);
    Font descriptionfont = this.destWorkbook.createFont();
    descriptionfont.setFontHeightInPoints(this.descFontHeight);
    this.descriptionStyle.setFont(descriptionfont);
    this.colHeadStyle = this.destWorkbook.createCellStyle();
    this.colHeadStyle.setAlignment((short) 2);
    this.colHeadStyle.setVerticalAlignment((short) 1);
    this.colHeadStyle.setWrapText(true);
    this.colHeadStyle.setFillForegroundColor(this.headForegroundColor);
    this.colHeadStyle.setFillPattern((short) 1);
    Font colHeadfont = this.destWorkbook.createFont();
    colHeadfont.setFontHeightInPoints(this.headFontHeight);
    colHeadfont.setBoldweight((short) 700);
    this.colHeadStyle.setFont(colHeadfont);
    this.colHeadStyle.setBorderBottom((short) 1);
    this.colHeadStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    this.colHeadStyle.setBorderLeft((short) 1);
    this.colHeadStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    this.colHeadStyle.setBorderRight((short) 1);
    this.colHeadStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
    this.colHeadStyle.setBorderTop((short) 1);
    this.colHeadStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
    this.rowHeadStyle = this.destWorkbook.createCellStyle();
    this.rowHeadStyle.setAlignment((short) 2);
    this.rowHeadStyle.setFillForegroundColor(this.headForegroundColor);
    this.rowHeadStyle.setFillPattern((short) 1);
    Font rowHeadfont = this.destWorkbook.createFont();
    rowHeadfont.setFontHeightInPoints(this.headFontHeight);
    rowHeadfont.setBoldweight((short) 700);
    this.rowHeadStyle.setFont(rowHeadfont);
    this.rowHeadStyle.setVerticalAlignment((short) 1);
    this.rowHeadStyle.setWrapText(true);
    this.rowHeadStyle.setBorderBottom((short) 1);
    this.rowHeadStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    this.rowHeadStyle.setBorderLeft((short) 1);
    this.rowHeadStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    this.rowHeadStyle.setBorderRight((short) 1);
    this.rowHeadStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
    this.rowHeadStyle.setBorderTop((short) 1);
    this.rowHeadStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
    this.stringDataCellStyle = this.destWorkbook.createCellStyle();
    Font stringDatafont = this.destWorkbook.createFont();
    stringDatafont.setFontHeightInPoints(this.dataFontHeight);
    this.stringDataCellStyle.setFont(stringDatafont);
    this.stringDataCellStyle.setVerticalAlignment((short) 1);
    this.stringDataCellStyle.setWrapText(true);
    this.stringDataCellStyle.setBorderBottom((short) 1);
    this.stringDataCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    this.stringDataCellStyle.setBorderLeft((short) 1);
    this.stringDataCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    this.stringDataCellStyle.setBorderRight((short) 1);
    this.stringDataCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
    this.stringDataCellStyle.setBorderTop((short) 1);
    this.stringDataCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
    this.integerDataCellStyle = this.destWorkbook.createCellStyle();
    Font integerDatafont = this.destWorkbook.createFont();
    integerDatafont.setFontHeightInPoints(this.dataFontHeight);
    this.integerDataCellStyle.setFont(integerDatafont);
    this.integerDataCellStyle.setVerticalAlignment((short) 1);
    this.integerDataCellStyle.setWrapText(true);
    this.integerDataCellStyle.setBorderBottom((short) 1);
    this.integerDataCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    this.integerDataCellStyle.setBorderLeft((short) 1);
    this.integerDataCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    this.integerDataCellStyle.setBorderRight((short) 1);
    this.integerDataCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
    this.integerDataCellStyle.setBorderTop((short) 1);
    this.integerDataCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
    DataFormat integerFormat = this.destWorkbook.createDataFormat();
    this.integerDataCellStyle.setDataFormat(integerFormat.getFormat("#,##0"));
    this.floatDataCellStyle = this.destWorkbook.createCellStyle();
    Font floatDatafont = this.destWorkbook.createFont();
    floatDatafont.setFontHeightInPoints(this.dataFontHeight);
    this.floatDataCellStyle.setFont(floatDatafont);
    this.floatDataCellStyle.setVerticalAlignment((short) 1);
    this.floatDataCellStyle.setWrapText(true);
    this.floatDataCellStyle.setBorderBottom((short) 1);
    this.floatDataCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    this.floatDataCellStyle.setBorderLeft((short) 1);
    this.floatDataCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    this.floatDataCellStyle.setBorderRight((short) 1);
    this.floatDataCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
    this.floatDataCellStyle.setBorderTop((short) 1);
    this.floatDataCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
    DataFormat floatFormat = this.destWorkbook.createDataFormat();
    this.floatDataCellStyle.setDataFormat(floatFormat.getFormat("#,##0.00"));
  }

  protected void fillTitle() {
    Sheet sheet = this.destWorkbook.getSheet(this.sheetName);
    Row row = sheet.createRow(this.rowNumber);
    row.setHeightInPoints((float) this.titleRowHeight);
    Cell titleCell = row.createCell(this.startCol, 1);
    titleCell.setCellValue(this.title);
    titleCell.setCellStyle(this.titleStyle);
    fillExcelCol(sheet);
  }

  private void fillExcelCol(Sheet sheet) {
    int mergedColCount = 8;
    if (this.rowDataList != null && !this.rowDataList.isEmpty()) {
      mergedColCount = this.rowDataList.get(0).size();
    } else if (this.headList != null && !this.headList.isEmpty()) {
      mergedColCount = this.headList.size();
    }

    sheet.addMergedRegion(new CellRangeAddress(this.rowNumber, this.rowNumber, this.startCol,
        this.startCol + mergedColCount - 1));
    this.rowNumber = this.rowNumber + 1;
  }

  protected void fillDiscription() {
    if (this.description != null && this.description.trim().length() > 0) {
      Sheet sheet = this.destWorkbook.getSheet(this.sheetName);
      Row row = sheet.createRow(this.rowNumber);
      row.setHeightInPoints((float) this.descRowHeight);
      Cell descriptionCell = row.createCell(this.startCol, 1);
      descriptionCell.setCellValue(this.description);
      descriptionCell.setCellStyle(this.descriptionStyle);
      fillExcelCol(sheet);
    }

  }

  protected void fillColHead() {
    Sheet sheet = this.destWorkbook.getSheet(this.sheetName);
    Row row = sheet.createRow(this.rowNumber);
    if (this.colheadRowHeigh != null) {
      row.setHeightInPoints((float) this.colheadRowHeigh);
    }

    int colHeadStartColNum = this.startCol;
    if (this.isDataFstHead && this.rowDataList != null && !this.rowDataList.isEmpty()) {
      if (this.crossHeadText != null) {
        Cell colHeadCell = row.createCell(colHeadStartColNum, 1);
        colHeadCell.setCellValue(this.crossHeadText);
        colHeadCell.setCellStyle(this.colHeadStyle);
      }

      ++colHeadStartColNum;
    }

    for (int i = 0; i < this.headList.size(); ++i) {
      Cell colHeadCell = row.createCell(colHeadStartColNum + i, 1);
      colHeadCell.setCellValue((String) this.headList.get(i));
      colHeadCell.setCellStyle(this.colHeadStyle);
    }

    this.rowNumber = this.rowNumber + 1;
  }

  protected void fillData() {
    Sheet sheet = this.destWorkbook.getSheet(this.sheetName);

    for (int i = 0; i < this.rowDataList.size(); ++i) {
      Row row = sheet.createRow(this.rowNumber);
      if (this.dataRowHeigh != null) {
        row.setHeightInPoints((float) this.dataRowHeigh);
      }

      List<Object> rowData = this.rowDataList.get(i);

      for (int j = 0; j < rowData.size(); ++j) {
        Cell cell = row.createCell(this.startCol + j);
        Object data = rowData.get(j);
        if (j == 0 && this.isDataFstHead) {
          cell.setCellType(1);
          cell.setCellStyle(this.rowHeadStyle);
          cell.setCellValue(data.toString());
        } else {
          data = getDataByCellType(data);

          fillNumberData(cell, data);

          if (this.dataCellAlign != null) {
            cell.getCellStyle().setAlignment(this.dataCellAlign);
          }
        }
      }

      this.rowNumber = this.rowNumber + 1;
    }

  }

  private Object getDataByCellType(Object data) {
    if (this.dataCellType != null) {
      if (DATA_CELL_TYPE_INTEGER.equals(this.dataCellType)) {
        data = Integer.parseInt(data.toString());
      } else if (DATA_CELL_TYPE_FLOAT.equals(this.dataCellType)) {
        data = Double.valueOf(data.toString());
      } else if (DATA_CELL_TYPE_STRING.equals(this.dataCellType)) {
        data = data.toString();
      }
    }
    return data;
  }

  private void fillNumberData(Cell cell, Object data) {
    if (data instanceof Number) {
      cell.setCellType(0);
      List<Class> integerClassList = new ArrayList();
      integerClassList.add(Integer.class);
      integerClassList.add(Long.class);
      integerClassList.add(Short.class);
      integerClassList.add(BigInteger.class);
      if (integerClassList.contains(data.getClass())) {
        cell.setCellStyle(this.integerDataCellStyle);
      } else {
        cell.setCellStyle(this.floatDataCellStyle);
      }

      cell.setCellValue(Double.parseDouble(data.toString()));
    } else {
      cell.setCellType(0);
      cell.setCellStyle(this.stringDataCellStyle);
      cell.setCellValue(data.toString());
    }
  }

}
